﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using System.Configuration;
using System.Data.Common;

namespace DataObjects
{
    public partial class DbObject
    {
        #region "Private static Property"
            //private static readonly string dataProvider = ConfigurationManager.AppSettings.Get("DataProvider");
            //private static readonly DbProviderFactory factory = DbProviderFactories.GetFactory(dataProvider);

            private static readonly string ConnectionStringName = ConfigurationManager.AppSettings.Get("ConnectionStringName");
            private static readonly string ConfigConnectionString = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
        #endregion

        #region "Member variable"
            private SqlConnection m_Connection;
            private string m_ConnectionString;
        #endregion

        #region "Public Property"
            public SqlConnection Connection
            {
                get
                {
                    if(m_Connection==null)
                        m_Connection = new SqlConnection(m_ConnectionString);
                    return m_Connection;
                }
                set
                {
                    m_Connection = value;
                }
            }
            public string ConnectionString
            {
                get
                {
                    return m_ConnectionString;
                }
                set
                {
                    m_ConnectionString = value;
                }
            }
        #endregion

        #region "Constructor"
            public DbObject()
            {
                this.ConnectionString = ConfigConnectionString;
                this.Connection = new SqlConnection(this.ConnectionString);
            }
            public DbObject(string newConnectionString)
            {
                this.ConnectionString = newConnectionString;
                this.Connection = new SqlConnection(this.ConnectionString);
                
            }
        #endregion

        #region "Private Method"
        private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, this.Connection);
            command.CommandTimeout = this.Connection.ConnectionTimeout;
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }
        #endregion

        #region "Public Method - RunProdure"
        public SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            this.Connection.Open();
            SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader();
        }

        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            this.Connection.Open();
            SqlCommand command = this.BuildIntCommand(storedProcName, parameters);
            rowsAffected = command.ExecuteNonQuery();
            int num = (int)command.Parameters["@ReturnValue"].Value;
            this.Connection.Close();
            return num;
        }

        public string RunProcedureExecuteScalar(string storedProcName, IDataParameter[] parameters)
        {
            this.Connection.Open();
            SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
            object ret = command.ExecuteScalar();
            string strRet = ret.ToString();
            this.Connection.Close();
            return strRet;
        }

        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            DataSet dataSet = new DataSet();
            this.Connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.BuildQueryCommand(storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
            this.Connection.Close();
            return dataSet;
        }

        public void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
        {
            this.Connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.BuildIntCommand(storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
            this.Connection.Close();
        }

        public DataSet RunQuery(string Query, string tableName)
        {
            DataSet dataSet = new DataSet();
            this.Connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand(Query, this.Connection);
            command.CommandType = CommandType.Text;
            adapter.SelectCommand = command;
            adapter.Fill(dataSet, tableName);
            this.Connection.Close();
            return dataSet;
        }
        public string RunQueryExecuteScalar(string Query)
        {
            string ret = "";
            this.Connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand(Query, this.Connection);
            command.CommandType = CommandType.Text;
            adapter.SelectCommand = command;
            ret = command.ExecuteScalar().ToString();
            this.Connection.Close();
            return ret;
        }
        public string RunQueryExecuteNonQuery(string Query)
        {
            string ret = "";
            this.Connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlCommand command = new SqlCommand(Query, this.Connection);
            command.CommandType = CommandType.Text;
            adapter.SelectCommand = command;
            ret = command.ExecuteNonQuery().ToString();
            this.Connection.Close();
            return ret;
        }
        #endregion

        #region "Public Method - RunProdure - No care connection"
        /// <summary>
        /// Run store - Open/Close connection manual
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader RunProcedureManualCon(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader();
        }

        /// <summary>
        /// Run store - Open/Close connection manual
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="rowsAffected"></param>
        /// <returns></returns>
        public int RunProcedureManualCon(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            SqlCommand command = this.BuildIntCommand(storedProcName, parameters);
            rowsAffected = command.ExecuteNonQuery();
            int num = (int)command.Parameters["ReturnValue"].Value;
            return num;
        }

        /// <summary>
        /// Run store - Open/Close connection manual
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet RunProcedureManualCon(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            DataSet dataSet = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.BuildQueryCommand(storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
            return dataSet;
        }

        /// <summary>
        /// Run store - Open/Close connection manual
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="dataSet"></param>
        /// <param name="tableName"></param>
        public void RunProcedureManualCon(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = this.BuildIntCommand(storedProcName, parameters);
            adapter.Fill(dataSet, tableName);
        }

        public DataSet RunQuery(string Query, string tableName, bool isCloseConnection)
        {
            if(isCloseConnection)
                return RunQuery(Query, tableName);
            else
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlCommand command = new SqlCommand(Query, this.Connection);
                command.CommandType = CommandType.Text;
                adapter.SelectCommand = command;
                adapter.Fill(dataSet, tableName);
                return dataSet;
            }
        }        
        #endregion
    }
}